package com.poispace.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedList;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;

import com.poispace.Poi;
import com.poispace.pojo.CellModel;

public class OneExcelReader {
    
    private Sheet sheet;    //表格类实例  
    LinkedList[] result;    //保存每个单元格的数据 ，使用的是一种链表数组的结构  
    Workbook workBook;
    String contents="";
    //读取excel文件，创建表格实例  
    private void loadExcel(String filePath) {  
        FileInputStream inStream = null;  
        try {  
            inStream = new FileInputStream(new File(filePath));  
             workBook = WorkbookFactory.create(inStream);  
             
            sheet = workBook.getSheetAt(0);           
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            try {  
                if(inStream!=null){  
                    inStream.close();  
                }                  
            } catch (IOException e) {                  
                e.printStackTrace();  
            }  
        }  
    }  
    //获取单元格的值  
    private String getCellValue(Cell cell) {  
        String cellValue = "";  
        StringBuffer content=new StringBuffer();
        String cellType = "";  
        CellAddress address =null;
        DataFormatter formatter = new DataFormatter();  
        int firstColumn =0 ;  
        int lastColumn = 0 ;  
        int firstRow = 0 ;  
        int lastRow =0;
        int firstColumn1 =0 ;  
        int lastColumn1 = 0 ;  
        int firstRow1 = 0 ;  
        int lastRow1 =0;
        String area="";
        if (cell != null) {  
        	
        	
        	  address=cell.getAddress();
        	int row=cell.getRowIndex();
        	int column=cell.getColumnIndex();
        	int sheetMergeCount = sheet.getNumMergedRegions();
        	 for(int i = 0 ; i < sheetMergeCount ; i++){  
                 CellRangeAddress ca = sheet.getMergedRegion(i);  
                 
                  firstColumn = ca.getFirstColumn();  
                  lastColumn = ca.getLastColumn();  
                  firstRow = ca.getFirstRow();  
                  lastRow = ca.getLastRow();  
                 if(row >= firstRow && row <= lastRow){  
                     if(column >= firstColumn && column <= lastColumn){  
//                         Row fRow = sheet.getRow(firstRow);  
//                         Cell fCell = fRow.getCell(firstColumn);  
                          area=""+(char)(firstColumn+65)+(int)(firstRow+1)+"-"+(char)(lastColumn+65)+(int)(lastRow+1);
                          firstRow1=firstRow;
                          firstColumn1=firstColumn;
                          lastRow1=lastRow;
                          lastColumn1=lastColumn;
                        
                     }  
                 }  
             }
       
            //判断单元格数据的类型，不同类型调用不同的方法  
            switch (cell.getCellType()) {  
            
                //数值类型  
                case Cell.CELL_TYPE_NUMERIC:  
                    //进一步判断 ，单元格格式是日期格式   
                    if (DateUtil.isCellDateFormatted(cell)) {  
                        cellValue = formatter.formatCellValue(cell);  
                        
                        cellType="日期格式";
                        
                    } else {  
                        //数值  
                    	cellType="数值格式";
                        double value = cell.getNumericCellValue();  
                        int intValue = (int) value;  
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);  
                    }  
                    break;  
                case Cell.CELL_TYPE_STRING: 
                	cellType="文本格式";
                    cellValue = cell.getStringCellValue();  
                    break;  
                case Cell.CELL_TYPE_BOOLEAN: 
                	cellType="布尔格式";
                    cellValue = String.valueOf(cell.getBooleanCellValue());  
                    break;  
                    //判断单元格是公式格式，需要做一种特殊处理来得到相应的值  
                case Cell.CELL_TYPE_FORMULA:{  
                	cellType="公式格式";
                    try{  
                        cellValue = String.valueOf(cell.getNumericCellValue());  
                    }catch(IllegalStateException e){  
                        cellValue = String.valueOf(cell.getRichStringCellValue());  
                    }  
                      
                }  
                    break;  
                case Cell.CELL_TYPE_BLANK:  
                	cellType="默认格式";
                    cellValue = "null";  
                    break;  
                case Cell.CELL_TYPE_ERROR:
                	cellType="错误格式";
                    cellValue = "null";  
                    break;  
                default:  
                    cellValue = cell.toString().trim();  
                    break;  
            }  
        }  
        if(address!=null){
         
        	if(address.getRow()>=firstRow1&&address.getColumn()>=firstColumn1&&address.getRow()<=lastRow1&&address.getColumn()<=lastColumn1){
        	 
            	if(address.getRow()==firstRow1&&address.getColumn()==firstColumn1){
            		content.append("单元格名称:"+address);
                    content.append("|内容:"+cellValue);
                    content.append("|数据格式:"+cellType);
                    content.append("|区域:"+area);
                    HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
                    
                    
                	content.append("|字体:"+cellStyle.getFont(workBook).getFontName());
                	content.append("|字号:"+cell.getCellStyle().getFontIndex());
                	 
                	HSSFColor color =cellStyle.getFillForegroundColorColor();
                	HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
//                	color.get
                	 short[] aa=color.getTriplet();
                	 short[] bb=color2.getTriplet();
                	content.append("|前景色:"+convertToStardColor(color));
                	content.append("|背景色:"+convertToStardColor(color2));
                	
                	if(cell.getCellStyle().getBorderBottom()==0&&cell.getCellStyle().getBorderTop()==0&&cell.getCellStyle().getBorderLeft()==0&&cell.getCellStyle().getBorderRight()==0){
                		content.append("|边框:无");
                	}else{
                		content.append("|边框:有");
                	}
                	content.append("|缩进:"+cell.getCellStyle().getIndention());
                	 
            	}
            }else{
            	content.append("单元格名称:"+address);
                content.append("|内容:"+cellValue);
                content.append("|数据格式:"+cellType);
                content.append("|区域:"+address+"-"+address);
                HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
                
                
            	content.append("|字体:"+cellStyle.getFont(workBook).getFontName());
            	content.append("|字号:"+cell.getCellStyle().getFontIndex());
            	HSSFColor color =cellStyle.getFillForegroundColorColor();
            	HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
//            	color.get
            	 short[] aa=color.getTriplet();
            	 short[] bb=color2.getTriplet();
            	content.append("|前景色:"+convertToStardColor(color));
            	content.append("|背景色:"+convertToStardColor(color2));
            	
            	if(cell.getCellStyle().getBorderBottom()==0&&cell.getCellStyle().getBorderTop()==0&&cell.getCellStyle().getBorderLeft()==0&&cell.getCellStyle().getBorderRight()==0){
            		content.append("|边框:无");
            	}else{
            		content.append("|边框:有");
            	}
            	content.append("|缩进:"+cell.getCellStyle().getIndention());
                
            }
        	
        	 
//        	System.err.println(content.toString());	
        }
        
        
        return content.toString();  
    }  
    
    
    //获取单元格的值  
    private CellModel getCellValueByModel(Cell cell) {  
    	CellModel model=new CellModel();
        String cellValue = "";  
        StringBuffer content=new StringBuffer();
        String cellType = "";  
        CellAddress address =null;
        DataFormatter formatter = new DataFormatter();  
        int firstColumn =0 ;  
        int lastColumn = 0 ;  
        int firstRow = 0 ;  
        int lastRow =0;
        int firstColumn1 =0 ;  
        int lastColumn1 = 0 ;  
        int firstRow1 = 0 ;  
        int lastRow1 =0;
        String area="";
        if (cell != null) {  
        	
        	
        	  address=cell.getAddress();
        	int row=cell.getRowIndex();
        	int column=cell.getColumnIndex();
        	int sheetMergeCount = sheet.getNumMergedRegions();
        	 for(int i = 0 ; i < sheetMergeCount ; i++){  
                 CellRangeAddress ca = sheet.getMergedRegion(i);  
                 
                  firstColumn = ca.getFirstColumn();  
                  lastColumn = ca.getLastColumn();  
                  firstRow = ca.getFirstRow();  
                  lastRow = ca.getLastRow();  
                 if(row >= firstRow && row <= lastRow){  
                     if(column >= firstColumn && column <= lastColumn){  
//                         Row fRow = sheet.getRow(firstRow);  
//                         Cell fCell = fRow.getCell(firstColumn);  
                          area=""+(char)(firstColumn+65)+(int)(firstRow+1)+"-"+(char)(lastColumn+65)+(int)(lastRow+1);
                          firstRow1=firstRow;
                          firstColumn1=firstColumn;
                          lastRow1=lastRow;
                          lastColumn1=lastColumn;
                        
                     }  
                 }  
             }
       
            //判断单元格数据的类型，不同类型调用不同的方法  
            switch (cell.getCellType()) {  
            
                //数值类型  
                case Cell.CELL_TYPE_NUMERIC:  
                    //进一步判断 ，单元格格式是日期格式   
                    if (DateUtil.isCellDateFormatted(cell)) {  
                        cellValue = formatter.formatCellValue(cell);  
                        
                        cellType="日期格式";
                        
                    } else {  
                        //数值  
                    	cellType="数值格式";
                        double value = cell.getNumericCellValue();  
                        int intValue = (int) value;  
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);  
                    }  
                    break;  
                case Cell.CELL_TYPE_STRING: 
                	cellType="文本格式";
                    cellValue = cell.getStringCellValue();  
                    break;  
                case Cell.CELL_TYPE_BOOLEAN: 
                	cellType="布尔格式";
                    cellValue = String.valueOf(cell.getBooleanCellValue());  
                    break;  
                    //判断单元格是公式格式，需要做一种特殊处理来得到相应的值  
                case Cell.CELL_TYPE_FORMULA:{  
                	cellType="公式格式";
                    try{  
                        cellValue = String.valueOf(cell.getNumericCellValue());  
                    }catch(IllegalStateException e){  
                        cellValue = String.valueOf(cell.getRichStringCellValue());  
                    }  
                      
                }  
                    break;  
                case Cell.CELL_TYPE_BLANK:  
                	cellType="默认格式";
                    cellValue = "null";  
                    break;  
                case Cell.CELL_TYPE_ERROR:
                	cellType="错误格式";
                    cellValue = "null";  
                    break;  
                default:  
                    cellValue = cell.toString().trim();  
                    break;  
            }  
        }  
        if(address!=null){
         
        	if(address.getRow()>=firstRow1&&address.getColumn()>=firstColumn1&&address.getRow()<=lastRow1&&address.getColumn()<=lastColumn1){
        	 
            	if(address.getRow()==firstRow1&&address.getColumn()==firstColumn1){
            		model.setCellName(address.toString());
            		model.setContent(cellValue);
            		model.setFormat(cellType);
            		model.setArea(area);
            		HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
            		model.setFont(cellStyle.getFont(workBook).getFontName());
            		model.setFontSize(cellStyle.getFont(workBook).getFontHeightInPoints()+"pt");
            		HSSFColor color =cellStyle.getFillForegroundColorColor();
            		model.setForegroundColor(convertToStardColor(color));
            		HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
            		model.setBackgroundColor(convertToStardColor(color2));
//            		if(cell.getCellStyle().getBorderBottom()==0&&cell.getCellStyle().getBorderTop()==0&&cell.getCellStyle().getBorderLeft()==0&&cell.getCellStyle().getBorderRight()==0){
//            			model.setFrame("无");
//                	}else{
//                		model.setFrame("|边框:有");
//                	}
            		model.setIndentation(cell.getCellStyle().getIndention());
//            		content.append("单元格名称:"+address);
//                    content.append("|内容:"+cellValue);
//                    content.append("|数据格式:"+cellType);
//                    content.append("|区域:"+area);
//                    HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
//                    
//                    
//                	content.append("|字体:"+cellStyle.getFont(workBook).getFontName());
//                	content.append("|字号:"+cell.getCellStyle().getFontIndex());
//                	 
//                	HSSFColor color =cellStyle.getFillForegroundColorColor();
//                	HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
////                	color.get
//                	 short[] aa=color.getTriplet();
//                	 short[] bb=color2.getTriplet();
//                	content.append("|前景色:"+convertToStardColor(color));
//                	content.append("|背景色:"+convertToStardColor(color2));
//                	
//                	if(cell.getCellStyle().getBorderBottom()==0&&cell.getCellStyle().getBorderTop()==0&&cell.getCellStyle().getBorderLeft()==0&&cell.getCellStyle().getBorderRight()==0){
//                		content.append("|边框:无");
//                	}else{
//                		content.append("|边框:有");
//                	}
//                	content.append("|缩进:"+cell.getCellStyle().getIndention());
                	 
            	}
            }else{
            	model.setCellName(address.toString());
        		model.setContent(cellValue);
        		model.setFormat(cellType);
        		model.setArea(area);
        		HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
        		model.setFont(cellStyle.getFont(workBook).getFontName());
        		model.setFontSize(cellStyle.getFont(workBook).getFontHeightInPoints()+"pt");
        		HSSFColor color =cellStyle.getFillForegroundColorColor();
        		model.setForegroundColor(convertToStardColor(color));
        		HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
        		model.setBackgroundColor(convertToStardColor(color2));
//        		if(cell.getCellStyle().getBorderBottom()==0&&cell.getCellStyle().getBorderTop()==0&&cell.getCellStyle().getBorderLeft()==0&&cell.getCellStyle().getBorderRight()==0){
//        			model.setFrame("无");
//            	}else{
//            		model.setFrame("|边框:有");
//            	}
        		model.setIndentation(cell.getCellStyle().getIndention());
//            	content.append("单元格名称:"+address);
//                content.append("|内容:"+cellValue);
//                content.append("|数据格式:"+cellType);
//                content.append("|区域:"+address+"-"+address);
//                HSSFCellStyle cellStyle =(HSSFCellStyle) cell.getCellStyle();
//                
//                
//            	content.append("|字体:"+cellStyle.getFont(workBook).getFontName());
//            	content.append("|字号:"+cell.getCellStyle().getFontIndex());
//            	HSSFColor color =cellStyle.getFillForegroundColorColor();
//            	HSSFColor color2 =cellStyle.getFillBackgroundColorColor();
////            	color.get
//            	 short[] aa=color.getTriplet();
//            	 short[] bb=color2.getTriplet();
//            	content.append("|前景色:"+convertToStardColor(color));
//            	content.append("|背景色:"+convertToStardColor(color2));
//            	
//            	if(cell.getCellStyle().getBorderBottom()==0&&cell.getCellStyle().getBorderTop()==0&&cell.getCellStyle().getBorderLeft()==0&&cell.getCellStyle().getBorderRight()==0){
//            		content.append("|边框:无");
//            	}else{
//            		content.append("|边框:有");
//            	}
//            	content.append("|缩进:"+cell.getCellStyle().getIndention());
                
            }
        	
        	 
//        	System.err.println(content.toString());	
        }
        
        
        return model;  
    }  
  
    //转换颜色代码
    private String convertToStardColor(HSSFColor hc) {  
        StringBuffer sb = new StringBuffer("");  
        if (hc != null) {  
//            int a = HSSFColor.AUTOMATIC.index;  
//            int b = hc.getIndex();  
//            if (a == b) {  
//                return null;  
//            }  
            sb.append("#");  
            for (int i = 0; i < hc.getTriplet().length; i++) {  
                String str;  
                String str_tmp = Integer.toHexString(hc.getTriplet()[i]);  
                if (str_tmp != null && str_tmp.length() < 2) {  
                    str = "0" + str_tmp;  
                } else {  
                    str = str_tmp;  
                }  
                sb.append(str);  
            }  
        }  
        return sb.toString();  
    }  
  
  
    //初始化表格中的每一行，并得到每一个单元格的值  
    public  String init(){  
        int rowNum = sheet.getLastRowNum() + 1;  
        result = new LinkedList[rowNum];  
        int sheetMergeCount = sheet.getNumMergedRegions();  
        for(int i=0;i<rowNum;i++){ 
        	int emptyrow=0;
        	int emptycolumn=0;
            Row row = sheet.getRow(i);  
            //每有新的一行，创建一个新的LinkedList对象  
            result[i] = new LinkedList();  
            for(int j=0;j<row.getLastCellNum();j++){  
                Cell cell = row.getCell(j);  
                //获取单元格的值  
                String str = getCellValue(cell).replace("\n", "").replace("\r", "").replace("\t", "");  
                
                
               
                if(str.contains("默认格式")){
                	emptycolumn=emptycolumn+1;
                }
                if(str.trim().equals("")){
                	contents=contents+str;
                }else{
                	if (System.getProperty("line.separator").equals("/r/n")) {    
                	    System.out.println("//r//n is for windows");    
                	} else if (System.getProperty("line.separator").equals("/r")) {    
                	    System.out.println("//r is for Mac");    
                	} else if (System.getProperty("line.separator").equals("/n")) {    
                	    System.out.println("//n is for Unix/Linux");    
                	} 
                	else{
                		System.err.println("获取不到 当前系统的换行符");
                	}
                	
                	 contents=contents+str+"\r";
                }
                if(emptycolumn==row.getLastCellNum()-1){
                	emptyrow++;
                }
                
            }  
            if(emptyrow==1){
             
            	break;
            }
        }  
        return contents ;
    }  
    public static String [] getFileName(String path)
    {
        File file = new File(path);
        String [] fileName = file.list();
        return fileName;
    }
 
    public static void main(String[] args) {  
    	
    	  

        
         
    }
    public  void readexcel(String readpath,String writepath,String string) {
		String [] allname= getFileName(readpath);
		if(string.substring(string.lastIndexOf(".")+1).equals("xls")){
			String excelname=string.substring(0, string.lastIndexOf("."));
			
			OneExcelReader poi = new OneExcelReader();  
	        poi.loadExcel(readpath+"\\"+excelname+".xls");  
//	        System.err.println(poi.init());
//	        poi.show();  
	        FileOutputStream fop = null;
	        File file;
	        String content = poi.init();

	        try {

	         file = new File(writepath+"\\"+excelname+".txt");
	         fop = new FileOutputStream(file);

	         // if file doesnt exists, then create it
	         if (!file.exists()) {
	          file.createNewFile();
	         }

	         // get the content in bytes
	         byte[] contentInBytes = content.getBytes();

	         fop.write(contentInBytes);
	         fop.flush();
	         fop.close();

	         System.out.println("Done");

	        } catch (IOException e) {
	         e.printStackTrace();
	        } finally {
	         try {
	          if (fop != null) {
	           fop.close();
	          }
	         } catch (IOException e) {
	          e.printStackTrace();
	         }
	        }
		}
	} 
}
